package de.busschedule.control;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import de.busschedule.entities.BusLine;
import de.busschedule.entities.BusStop;
import de.busschedule.logging.Logger;

@SuppressLint("SdCardPath")
public class DBHandler extends SQLiteOpenHelper{
	// Database Name
	private static final String DB_NAME = "BusScheduleOS.db";
	// The Android's default system path of your application database.
	private static String DB_PATH;
	private SQLiteDatabase db;
	private final Context context;

	// The tables
	private final String TABLE_LINE = "busline";
	private final String TABLE_STOP = "busstop";
	private final String TABLE_TIMES = "times";
	private final String TABLE_LOCATIONS = "locations";

	// Common columns
	private final String KEY_ID = "_id";
	private final String COLUMN_NAME = "name";

	// The tables' columns
	private final String LINE_NUMBER = "number";
	private final String LINE_TYPE = "type";
	private final String LINE_SRC = "src";
	private final String LINE_DEST = "dest";
	private final String LINE_STOPS = "stops";
	private final String LINE_VIA = "via";
	private final String LINE_DESC = "description";
	private final String LINE_COLOR = "color";
	private final String LINE_PATH = "filepath";

	private final String STOP_LOCATION = "location";
	private final String STOP_GPSIN = "gps_in";
	private final String STOP_GPSOUT = "gps_out";
	private final String STOP_LINES = "lines";
	private final String STOP_MAIN = "main";

	private final String TIMES_LINE_ID = "line_id";
	private final String TIMES_STOP_ID = "stop_id";
	private final String TIMES_TIME_NORMAL = "time_normal";
	private final String TIMES_TIME_SAT = "time_sat";
	private final String TIMES_TIME_SUN = "time_sun";

	private final String TAG = "DBHandler"; // Logging Tag

	public DBHandler(Context context){
		super(context, DB_NAME, null, 1);
		if(android.os.Build.VERSION.SDK_INT >= 17){
			DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
		}else{
			DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
		}
		this.context = context;
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 * */
	public void createDataBase() throws IOException{
		boolean dbExist = false;// checkDataBase(); //TODO: When database is
								// done, uncomment
		if(dbExist){
			Logger.debug(TAG, "Loading existing database");
		}else{
			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			this.getReadableDatabase();
			copyDataBase();
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase(){
		SQLiteDatabase checkDB = null;
		try{
			String path = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(path, null,
					SQLiteDatabase.OPEN_READONLY);
		}catch(SQLiteException e){
			Logger.error(TAG, "Database does not exist yet.");
		}
		if(checkDB != null){
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase(){
		// Open your local db as the input stream
		InputStream myInput;
		try{
			myInput = context.getAssets().open(DB_NAME);

			// Path to the just created empty db
			String outFileName = DB_PATH + DB_NAME;
			// Open the empty db as the output stream
			OutputStream myOutput = new FileOutputStream(outFileName);

			// transfer bytes from the inputfile to the outputfile
			byte[] buffer = new byte[1024];
			int length;
			while((length = myInput.read(buffer)) > 0){
				myOutput.write(buffer, 0, length);
			}

			// Close the streams
			myOutput.flush();
			myOutput.close();
			myInput.close();
		}catch(IOException e){
			Logger.error(TAG, "Could not copy database: " + e.getMessage());
		}
	}

	public void openDataBase() throws SQLException{
		// Open the database
		String myPath = DB_PATH + DB_NAME;
		db = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);
	}

	@Override
	public synchronized void close(){
		if(db != null)
			db.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db){}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){}

	/**
	 * Gets all stops from the line with ID id
	 * 
	 * @param id
	 *            the line's ID
	 * @return List of Bus Stops
	 */
	public List<BusStop> getStopsFromLine(String id){
		Cursor cursor = db.query(TABLE_LINE, new String[]{LINE_STOPS},
				KEY_ID + " = ?", new String[]{id}, null, null, null);

		String stops = "";
		if(cursor != null){
			if(cursor.moveToFirst()){
				do{
					stops = cursor.getString(cursor.getColumnIndex(LINE_STOPS));
				}while(cursor.moveToNext());
			}
		}
		cursor.close();
		String[] data = stops.split(AppConstants.delimiter);
		/*
		 * for(int i = 0; i < data.length; i++){ System.out.println("data: " +
		 * data[i]); }
		 */

		List<BusStop> stopList = new ArrayList<BusStop>();
		for(int i = 0; i < data.length; i++){
			cursor = db
					.query(TABLE_STOP,
							new String[]{KEY_ID, COLUMN_NAME, STOP_LOCATION, STOP_GPSIN, STOP_GPSOUT, STOP_LINES, STOP_MAIN},
							KEY_ID + "=?", new String[]{data[i]}, null, null,
							null);
			if(cursor != null){
				if(cursor.moveToFirst()){
					do{
						BusStop temp = new BusStop();
						// Set values for Bus Stops
						temp.setId(cursor.getString(cursor
								.getColumnIndex(KEY_ID)));
						temp.setName(cursor.getString(cursor
								.getColumnIndex(COLUMN_NAME)));
						temp.setLocation(cursor.getString(cursor
								.getColumnIndex(STOP_LOCATION)));
						temp.setGpsIn(cursor.getString(cursor
								.getColumnIndex(STOP_GPSIN)));
						temp.setGpsOut(cursor.getString(cursor
								.getColumnIndex(STOP_GPSOUT)));
						temp.setMain(cursor.getString(cursor
								.getColumnIndex(STOP_MAIN)));
						temp.setLines(new ArrayList<String>());
						String[] tempLines = cursor.getString(
								cursor.getColumnIndex(STOP_LINES)).split(
								AppConstants.delimiter);
						for(int j = 0; j < tempLines.length; j++){
							temp.getLines().add(tempLines[j]);
						}
						// Add values to the List
						stopList.add(temp);
					}while(cursor.moveToNext());
				}
			}
		}
		cursor.close();
		return stopList;
	}

	public Map<String, String> readBusses(String type){
		Map<String, String> temp = new HashMap<String, String>();
		Cursor cursor = db.query(TABLE_LINE,
				new String[]{KEY_ID, LINE_NUMBER, LINE_DEST},
				LINE_TYPE + " = ?", new String[]{type}, null, null, null);
		if(cursor != null){
			if(cursor.moveToFirst()){
				do{
					// Extract key and value
					String key;
					String value;
					key = cursor.getString(cursor.getColumnIndex(KEY_ID));
					value = cursor
							.getString(cursor.getColumnIndex(LINE_NUMBER)) + " " + cursor
							.getString(cursor.getColumnIndex(LINE_DEST));
					// Write to map
					temp.put(key, value);
				}while(cursor.moveToNext());
			}
		}
		cursor.close();
		printMap(temp);
		return temp;
	}

	public BusLine getLine(String id){
		BusLine temp = new BusLine();
		Cursor cursor = db
				.query(TABLE_LINE,
						new String[]{KEY_ID, LINE_TYPE, LINE_NUMBER, LINE_SRC, LINE_DEST, LINE_STOPS, LINE_VIA, LINE_DESC, LINE_COLOR, LINE_PATH},
						KEY_ID + " = ?", new String[]{id}, null, null, null);
		if(cursor != null){
			if(cursor.moveToFirst()){
				do{
					temp.setId(cursor.getString(cursor.getColumnIndex(KEY_ID)));
					temp.setType(cursor.getString(cursor
							.getColumnIndex(LINE_TYPE)));
					temp.setNumber(cursor.getString(cursor
							.getColumnIndex(LINE_NUMBER)));
					temp.setSource(cursor.getString(cursor
							.getColumnIndex(LINE_SRC)));
					temp.setDestination(cursor.getString(cursor
							.getColumnIndex(LINE_DEST)));
					temp.setVia(cursor.getString(cursor
							.getColumnIndex(LINE_VIA)));
					temp.setDescription(cursor.getString(cursor
							.getColumnIndex(LINE_DESC)));
					temp.setColor(cursor.getString(cursor
							.getColumnIndex(LINE_COLOR)));
					temp.setStops(cursor.getString(
							cursor.getColumnIndex(LINE_STOPS)).split(
							AppConstants.delimiter));
					temp.setFilepath(cursor.getString(cursor
							.getColumnIndex(LINE_PATH)));
				}while(cursor.moveToNext());
			}
		}
		cursor.close();
		return temp;
	}

	public List<String> getStopNamesAndTimes(BusLine line, boolean important){
		// Build the list
		List<String> stopList = new ArrayList<String>();

		Cursor cursor = null;
		for(int i = 0; i < line.getStops().length; i++){
			if(important){
				cursor = db.query(TABLE_STOP, new String[]{COLUMN_NAME},
						KEY_ID + "=? and " + STOP_MAIN + "=?",
						new String[]{line.getStops()[i], "yes"}, null, null,
						null);
			}else{
				cursor = db.query(TABLE_STOP, new String[]{COLUMN_NAME},
						KEY_ID + " = ?", new String[]{line.getStops()[i]},
						null, null, null);
			}
			if(cursor != null){
				if(cursor.moveToFirst()){
					// do{
					String stop = cursor.getString(cursor
							.getColumnIndex(COLUMN_NAME));
					if(stop != null){
						stop += "#" + getNextDepartureTime(line.getId(),
								line.getStops()[i]);
						// Add stop and respective time to the lists
						stopList.add(stop);
					}
					// }while(cursor.moveToNext());
				}
			}
		}
		cursor.close();
		return stopList;
	}

	private String getNextDepartureTime(String lineID, String stopID){
		String departure = "13:37";
		// TODO: Get next time
		return departure;
	}

	public Map<String, String> getStopsGPS(BusLine line){
		Map<String, String> temp = new HashMap<String, String>();

		Cursor cursor = null;
		boolean switchDirection = false;
		for(int i = 0; i < line.getStops().length; i++){
			cursor = db.query(TABLE_STOP,
					new String[]{KEY_ID, COLUMN_NAME, STOP_GPSIN, STOP_GPSOUT},
					KEY_ID + " = ?", new String[]{line.getStops()[i]}, null,
					null, null);
			if(cursor != null){
				if(cursor.moveToFirst()){
					String id = cursor.getString(cursor.getColumnIndex(KEY_ID));
					String stopName = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
					String stopGPSIn = cursor.getString(cursor.getColumnIndex(STOP_GPSIN));
					String stopGPSOut = cursor.getString(cursor.getColumnIndex(STOP_GPSOUT));
					// Add stop and respective GPS to the map
					// If we are at Neumarkt switch stop direction to GPSOut
					if(stopName.contains("Neumarkt Bussteig") || stopName.contains("Kamp-Promenade")){
						switchDirection = true;
					}
					if(switchDirection){
						temp.put(id, stopName + "#" + stopGPSOut); // Bus goes out of the city
					}else{
						temp.put(id, stopName + "#" + stopGPSIn); // Bus goes into the city
					}
				}
			}
		}
		cursor.close();
		return temp;
	}

	public Map<String, String> getAllStopsGPS(){
		Map<String, String> temp = new HashMap<String, String>();
		Cursor cursor = null;
		cursor = db.query(TABLE_STOP,
				new String[]{KEY_ID, COLUMN_NAME, STOP_GPSIN, STOP_GPSOUT},
				null, null, null,
				null, null);
		if(cursor != null){
			if(cursor.moveToFirst()){
				do{
					String id = cursor.getString(cursor.getColumnIndex(KEY_ID));
					String stopName = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
					String stopGPSIn = cursor.getString(cursor.getColumnIndex(STOP_GPSIN));
					String stopGPSOut = cursor.getString(cursor.getColumnIndex(STOP_GPSOUT));
					// Add stop and respective GPS to the map
					temp.put(id, stopName + "#" + stopGPSIn); // Bus goes into the city
					temp.put(" " + id + " ", stopName + "#" + stopGPSOut); // Bus goes out of the city
				}while(cursor.moveToNext());
			}
		}
		cursor.close();
		return temp;
	}

	public static String[] clean(final String[] v){
		List<String> list = new ArrayList<String>(Arrays.asList(v));
		list.removeAll(Collections.singleton(null));
		return list.toArray(new String[list.size()]);
	}

	protected void printList(List<?> list){
		System.out.println("====== PrintList =======");
		for(int i = 0; i < list.size(); i++){
			System.out.println(i + ": " + list.get(i).toString());
		}
	}

	protected void printMap(Map<String, String> map){
		System.out.println("====== PrintMap =======");
		for(Map.Entry<String, String> entry : map.entrySet()){
			System.out.println("Key: " + entry.getKey() + " Value: " + entry
					.getValue());
		}
	}
}
